#include <QCoreApplication>
#include <QTimer>
#include <QDateTime>
#include <QtSql>
#include <QDebug>

#include "coordinator/coordinator.h"
#include "datastorage/datastorage.h"

DataStorage::DataStorage(QString db_name, QObject *parent)
    : QObject(parent)
{
	m_database_name = db_name;

	db_init();
}

DataStorage::~DataStorage()
{
	
}

void DataStorage::db_init()
{
	if (QSqlDatabase::contains(m_database_name))
		return;

    m_db = QSqlDatabase::addDatabase(DB_SQL_DRIVER);
    m_db.setHostName(DB_COMPANY_NAME);
    m_db.setDatabaseName(m_database_name);
    m_db.setUserName(DB_USER_NAME);
    m_db.setPassword(DB_PASSWORD);
    if (m_db.open())
	    db_table_init();
}

void DataStorage::db_table_init()
{
    QString query_str;
    QSqlQuery query;

    // Create operator table in database
    query_str = "CREATE TABLE operator("
                      "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "name VARCHAR(20) UNIQUE,"
                      "terminal_name VARCHAR(20),"
                      "password VARCHAR(20),"
                      "fingerid INT,"
                      "isadmin INT"
                      ")";
    query = QSqlQuery(m_db);
    if (query.exec(query_str)) {
        qInfo() << "[STORAGE]Create table succeed";
	    query_str = "INSERT INTO "
	    			"operator ("
		    			"name, password, terminal_name, fingerid, isadmin"
		            ") "
	                "VALUES ("
		    			":name, :password, :terminal_name, :fingerid, :isadmin"
	                ")";
	    query.prepare(query_str);
	    query.bindValue(":name", ADMIN_USER_NAME);
	    query.bindValue(":password", ADMIN_PASSWORD);
	    query.bindValue(":terminal_name", "");
	    query.bindValue(":fingerid", 0);
	    query.bindValue(":isadmin", 1);
	    if (!query.exec())
	    	qInfo() << "[STORAGE]Append record failed";
	    query.prepare(query_str);
	    query.bindValue(":name", "abcd");
	    query.bindValue(":password", "1234");
	    query.bindValue(":terminal_name", "");
	    query.bindValue(":fingerid", 1);
	    query.bindValue(":isadmin", 0);
	    if (!query.exec())
	    	qInfo() << "[STORAGE]Append record failed";
    }

    // Create transaction table in database
    query_str = "CREATE TABLE trans("
                      "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "type INT,"
                      "epoch INT,"
                      "user_name VARCHAR(20),"
                      "client_name VARCHAR(40),"
                      "terminal_name VARCHAR(40),"
                      "terminal_id VARCHAR(16),"
                      "note_count01 INT,"
                      "note_count02 INT,"
                      "note_count03 INT,"
                      "note_count04 INT,"
                      "note_count05 INT,"
                      "note_count06 INT,"
                      "note_count07 INT,"
                      "note_count08 INT,"
                      "note_count09 INT,"
                      "note_count10 INT,"
                      "note_count INT,"
                      "note_amount INT,"
                      "status INT"
                      ")";
    query = QSqlQuery(m_db);
    if (query.exec(query_str)) {
        qInfo() << "[STORAGE]Create table succeed";
    }

    // Create sub-transaction table in database
    query_str = "CREATE TABLE sub_trans("
                      "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "type INT,"
                      "epoch INT,"
                      "user_name VARCHAR(20),"
                      "client_name VARCHAR(40),"
                      "terminal_name VARCHAR(40),"
                      "terminal_id VARCHAR(16),"
                      "note_count01 INT,"
                      "note_count02 INT,"
                      "note_count03 INT,"
                      "note_count04 INT,"
                      "note_count05 INT,"
                      "note_count06 INT,"
                      "note_count07 INT,"
                      "note_count08 INT,"
                      "note_count09 INT,"
                      "note_count10 INT,"
                      "note_count INT,"
                      "note_amount INT,"
                      "status INT"
                      ")";
    query = QSqlQuery(m_db);
    if (query.exec(query_str)) {
        qInfo() << "[STORAGE]Create table succeed";
//		db_test();
    }

    // Create failed sub-transaction table in database
    query_str = "CREATE TABLE failed_sub_trans("
                      "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "type INT,"
                      "epoch INT,"
                      "user_name VARCHAR(20),"
                      "client_name VARCHAR(40),"
                      "terminal_name VARCHAR(40),"
                      "terminal_id VARCHAR(16),"
                      "note_count01 INT,"
                      "note_count02 INT,"
                      "note_count03 INT,"
                      "note_count04 INT,"
                      "note_count05 INT,"
                      "note_count06 INT,"
                      "note_count07 INT,"
                      "note_count08 INT,"
                      "note_count09 INT,"
                      "note_count10 INT,"
                      "note_count INT,"
                      "note_amount INT,"
                      "status INT"
                      ")";
    query = QSqlQuery(m_db);
    if (query.exec(query_str))
        qInfo() << "[STORAGE]Create table succeed";

    // Create clear table in database
    query_str = "CREATE TABLE clrec("
                      "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "epoch INT,"
                      "note_count INT,"
                      "note_amount INT,"
                      "coin_count INT,"
                      "coin_amount INT,"
                      "amount INT"
                      ")";
    query = QSqlQuery(m_db);
	query.prepare(query_str);
	if (query.exec())
        qInfo() << "[STORAGE]Create table succeed";

    TransInfo saved_trans;
    db_merge_sub_transaction(saved_trans);
}

/*	QDateTime date_time;
	QString user_name;
	QString client_name;
	QString terminal_name;
	QString terminal_id;

	QString account_id;

	NoteInfo note_info;
*/
uint32_t DataStorage::db_append(TransInfo trans_info)
{
    QString query_str;
	QSqlQuery query;
    uint32_t trans_id = 0;

    // Inquiry database
    query_str = "INSERT INTO "
    			"trans ("
	    			"type, epoch, user_name, client_name, terminal_name, terminal_id,"
	    			"note_count01, note_count02, note_count03, note_count04, note_count05, note_count06, note_count07, note_count08, note_count09, note_count10,"
	    			"note_count, note_amount,"
	                "status"
	            ") "
                "VALUES ("
	    			":type, :epoch, :user_name, :client_name, :terminal_name, :terminal_id,"
	    			":note_count01, :note_count02, :note_count03, :note_count04, :note_count05, :note_count06, :note_count07, :note_count08, :note_count09, :note_count10,"
	    			":note_count, :note_amount,"
	                ":status"
                ")";
    m_query = QSqlQuery(m_db);
    m_query.prepare(query_str);
    m_query.bindValue(":type", trans_info.note_info.trans_type);
    m_query.bindValue(":epoch", trans_info.date_time.toSecsSinceEpoch());
    m_query.bindValue(":user_name", trans_info.user_name);
    m_query.bindValue(":client_name", trans_info.client_name);
    m_query.bindValue(":terminal_name", trans_info.terminal_name);
    m_query.bindValue(":terminal_id", trans_info.terminal_id);
    m_query.bindValue(":note_count01", trans_info.note_info.note_count[0]);
    m_query.bindValue(":note_count02", trans_info.note_info.note_count[1]);
    m_query.bindValue(":note_count03", trans_info.note_info.note_count[2]);
    m_query.bindValue(":note_count04", trans_info.note_info.note_count[3]);
    m_query.bindValue(":note_count05", trans_info.note_info.note_count[4]);
    m_query.bindValue(":note_count06", trans_info.note_info.note_count[5]);
    m_query.bindValue(":note_count07", trans_info.note_info.note_count[6]);
    m_query.bindValue(":note_count08", trans_info.note_info.note_count[7]);
    m_query.bindValue(":note_count09", trans_info.note_info.note_count[8]);
    m_query.bindValue(":note_count10", trans_info.note_info.note_count[9]);
    m_query.bindValue(":note_count", trans_info.note_info.total_count);
    m_query.bindValue(":note_amount", trans_info.note_info.total_amount);
    m_query.bindValue(":status", trans_info.status);
    if (!m_query.exec())
    	qInfo() << "[STORAGE]Append record failed";
    else {
    	query_str = QString("SELECT rowid FROM trans WHERE epoch = %1").arg(trans_info.date_time.toSecsSinceEpoch());
	    query = QSqlQuery(m_db);
	    if (query.exec(query_str)) {
			if (query.next())
				trans_id = query.value("rowid").toUInt();
    	}
    }

	return trans_id;
}

bool DataStorage::db_query(QDate start, QDate stop)
{
	qint64 epoch_start, epoch_stop;
    QString query_str;

    epoch_start = start.startOfDay().toSecsSinceEpoch();
    epoch_stop = stop.endOfDay().toSecsSinceEpoch();
    // Inquiry database
    query_str = QString("SELECT * FROM trans WHERE epoch >= %1 AND epoch <= %2 ORDER BY epoch").arg(epoch_start).arg(epoch_stop);
    m_query = QSqlQuery(m_db);
    if (!m_query.exec(query_str)) {
    	qInfo() << "[STORAGE]Query table failed" << query_str;
    	return false;
    }

    return true;
}

bool DataStorage::db_query_next(TransInfo &trans_info)
{
	if (!m_query.next())
        return false;

    db_fetch_row(&m_query, trans_info);
    return true;
}

bool DataStorage::db_query_nth(uint32_t idx, TransInfo &trans_info)
{
	if (m_query.seek(idx - 1)) {
    	db_fetch_row(&m_query, trans_info);
		return true;
	}

	return false;
}

void DataStorage::db_fetch_row(QSqlQuery *query, TransInfo &trans_info)
{
	trans_info.id = query->value("rowid").toUInt();
	trans_info.date_time = QDateTime::fromSecsSinceEpoch(query->value("epoch").toUInt());
	trans_info.user_name = query->value("user_name").toString();
	trans_info.client_name = query->value("client_name").toString();
	trans_info.terminal_name = query->value("terminal_name").toString();
	trans_info.terminal_id = query->value("terminal_id").toString();
	trans_info.note_info.trans_type = query->value("type").toUInt();
	trans_info.note_info.note_count[0] = query->value("note_count01").toUInt();
	trans_info.note_info.note_count[1] = query->value("note_count02").toUInt();
	trans_info.note_info.note_count[2] = query->value("note_count03").toUInt();
	trans_info.note_info.note_count[3] = query->value("note_count04").toUInt();
	trans_info.note_info.note_count[4] = query->value("note_count05").toUInt();
	trans_info.note_info.note_count[5] = query->value("note_count06").toUInt();
	trans_info.note_info.note_count[6] = query->value("note_count07").toUInt();
	trans_info.note_info.note_count[7] = query->value("note_count08").toUInt();
	trans_info.note_info.note_count[8] = query->value("note_count09").toUInt();
	trans_info.note_info.note_count[9] = query->value("note_count10").toUInt();
	trans_info.note_info.total_count = query->value("note_count").toUInt();
	trans_info.note_info.total_amount = query->value("note_amount").toUInt();
    trans_info.status = query->value("status").toUInt();

	for (uint32_t k = 0; k < NOTE_TYPE_COUNT; k++)
        trans_info.note_info.note_amount[k] = trans_info.note_info.note_count[k] * NOTE_factor(k);
}

void DataStorage::db_total_transaction(ClearInfo &clr_info)
{
    QString query_str;
	QSqlQuery query;

	query = QSqlQuery(m_db);

	query_str = QString("SELECT SUM(note_count), SUM(note_amount) FROM trans WHERE (type % 10) = 0");
	query.prepare(query_str);
	query.exec();
	if (query.next()) {
		clr_info.note_count = query.value(0).toUInt();
		clr_info.note_amount = query.value(1).toUInt();
	}
	query_str = QString("SELECT SUM(note_count), SUM(note_amount) FROM trans WHERE (type % 10) = 1");
	query.prepare(query_str);
	query.exec();
	if (query.next()) {
		clr_info.coin_count = query.value(0).toUInt();
		clr_info.coin_amount = query.value(1).toUInt();
	}
	query_str = QString("SELECT SUM(note_amount) FROM trans");
	query.prepare(query_str);
	query.exec();
	if (query.next()) {
		clr_info.amount = query.value(0).toUInt();
	}
}

void DataStorage::db_clear_transaction()
{
    QString query_str;
	QSqlQuery query;
	ClearInfo clr_info;

	db_total_transaction(clr_info);
	clr_info.date_time = QDateTime::currentDateTime();
	db_clr_append(clr_info);

	query_str = QString("DELETE FROM trans WHERE epoch != 0");
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();
}

bool DataStorage::db_query_not_uploaded(QList<TransInfo> &trans_info_list)
{
    QString query_str;
	QSqlQuery query;
	TransInfo trans_info;
	uint32_t count;

	trans_info_list.clear();

    // Inquiry database
    query_str = QString("SELECT * FROM trans WHERE status = 0 ORDER BY epoch");
    query = QSqlQuery(m_db);
    query.prepare(query_str);
    query.exec();
    count = 0;
	while (query.next()) {
		db_fetch_row(&query, trans_info);
		trans_info_list.append(trans_info);
		count++;
		if (count == 5)
			break;
	}

    return (trans_info_list.length() != 0);
}

void DataStorage::db_transaction_uploaded(uint32_t id)
{
    QString query_str;
	QSqlQuery query;

	query_str = QString("UPDATE trans SET status = 1 WHERE rowid = %1").arg(id);
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();
}

void DataStorage::db_clear_sub_transaction()
{
    QString query_str;
	QSqlQuery query;

	query_str = QString("DELETE FROM sub_trans WHERE epoch != 0");
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();
}

void DataStorage::db_append_sub_transaction(TransInfo trans_info)
{
    QString query_str;
	QSqlQuery query;

    // Inquiry database
    query_str = "INSERT INTO "
    			"sub_trans ("
	    			"type, epoch, user_name, client_name, terminal_name, terminal_id,"
	    			"note_count01, note_count02, note_count03, note_count04, note_count05, note_count06, note_count07, note_count08, note_count09, note_count10,"
	    			"note_count, note_amount,"
	                "status"
	            ") "
                "VALUES ("
	    			":type, :epoch, :user_name, :client_name, :terminal_name, :terminal_id,"
	    			":note_count01, :note_count02, :note_count03, :note_count04, :note_count05, :note_count06, :note_count07, :note_count08, :note_count09, :note_count10,"
	    			":note_count, :note_amount,"
	                ":status"
                ")";
    query = QSqlQuery(m_db);
    query.prepare(query_str);
    query.bindValue(":type", trans_info.note_info.trans_type);
    query.bindValue(":epoch", trans_info.date_time.toSecsSinceEpoch());
    query.bindValue(":user_name", trans_info.user_name);
    query.bindValue(":client_name", trans_info.client_name);
    query.bindValue(":terminal_name", trans_info.terminal_name);
    query.bindValue(":terminal_id", trans_info.terminal_id);
    query.bindValue(":note_count01", trans_info.note_info.note_count[0]);
    query.bindValue(":note_count02", trans_info.note_info.note_count[1]);
    query.bindValue(":note_count03", trans_info.note_info.note_count[2]);
    query.bindValue(":note_count04", trans_info.note_info.note_count[3]);
    query.bindValue(":note_count05", trans_info.note_info.note_count[4]);
    query.bindValue(":note_count06", trans_info.note_info.note_count[5]);
    query.bindValue(":note_count07", trans_info.note_info.note_count[6]);
    query.bindValue(":note_count08", trans_info.note_info.note_count[7]);
    query.bindValue(":note_count09", trans_info.note_info.note_count[8]);
    query.bindValue(":note_count10", trans_info.note_info.note_count[9]);
    query.bindValue(":note_count", trans_info.note_info.total_count);
    query.bindValue(":note_amount", trans_info.note_info.total_amount);
    query.bindValue(":status", trans_info.status);
    if (!query.exec())
    	qInfo() << "[STORAGE]Append record failed";
}

void DataStorage::db_merge_sub_transaction(TransInfo &trans_info)
{
    QString query_str;
	QSqlQuery query;
	uint32_t trans_id = 0;
	bool sub_trans_exists = false;

    for (uint32_t k = 0; k < NOTE_TYPE_COUNT; k++) {
        trans_info.note_info.note_amount[k] = 0;
        trans_info.note_info.note_count[k] = 0;
    }
    trans_info.note_info.total_count = 0;
    trans_info.note_info.total_amount = 0;

    // Inquiry database
    query_str = QString("SELECT * FROM sub_trans ORDER BY epoch");
    query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();

	while (query.next()) {
		TransInfo sub_trans;

        trans_info.date_time = QDateTime::fromSecsSinceEpoch(query.value("epoch").toUInt());
        trans_info.user_name = query.value("user_name").toString();
        trans_info.client_name = query.value("client_name").toString();
        trans_info.terminal_name = query.value("terminal_name").toString();
        trans_info.terminal_id = query.value("terminal_id").toString();
        trans_info.note_info.trans_type = query.value("type").toUInt();

        sub_trans.note_info.note_count[0] = query.value("note_count01").toUInt();
		sub_trans.note_info.note_count[1] = query.value("note_count02").toUInt();
		sub_trans.note_info.note_count[2] = query.value("note_count03").toUInt();
		sub_trans.note_info.note_count[3] = query.value("note_count04").toUInt();
		sub_trans.note_info.note_count[4] = query.value("note_count05").toUInt();
		sub_trans.note_info.note_count[5] = query.value("note_count06").toUInt();
		sub_trans.note_info.note_count[6] = query.value("note_count07").toUInt();
		sub_trans.note_info.note_count[7] = query.value("note_count08").toUInt();
		sub_trans.note_info.note_count[8] = query.value("note_count09").toUInt();
		sub_trans.note_info.note_count[9] = query.value("note_count10").toUInt();
		sub_trans.note_info.total_count = query.value("note_count").toUInt();
		sub_trans.note_info.total_amount = query.value("note_amount").toUInt();
	    sub_trans.status = query.value("status").toUInt();

		for (uint32_t k = 0; k < NOTE_TYPE_COUNT; k++)
	        sub_trans.note_info.note_amount[k] = sub_trans.note_info.note_count[k] * NOTE_factor(k);

		for (uint32_t k = 0; k < NOTE_TYPE_COUNT; k++) {
			trans_info.note_info.note_count[k] += sub_trans.note_info.note_count[k];
		    trans_info.note_info.note_amount[k] += sub_trans.note_info.note_amount[k];
		}
		trans_info.note_info.total_count += sub_trans.note_info.total_count;
		trans_info.note_info.total_amount += sub_trans.note_info.total_amount;

		sub_trans_exists = true;
	}

	if (sub_trans_exists) {
        trans_info.status = TRANS_STATUS_NOT_UPLOADED;
		trans_id = db_append(trans_info);
		trans_info.id = trans_id;
		db_clear_sub_transaction();
	}
}

void DataStorage::db_append_failed_sub_transaction(TransInfo trans_info)
{
    QString query_str;
	QSqlQuery query;

    // Inquiry database
    query_str = "INSERT INTO "
    			"failed_sub_trans ("
	    			"type, epoch, user_name, client_name, terminal_name, terminal_id,"
	    			"note_count01, note_count02, note_count03, note_count04, note_count05, note_count06, note_count07, note_count08, note_count09, note_count10,"
	    			"note_count, note_amount,"
	                "status"
	            ") "
                "VALUES ("
	    			":type, :epoch, :user_name, :client_name, :terminal_name, :terminal_id,"
	    			":note_count01, :note_count02, :note_count03, :note_count04, :note_count05, :note_count06, :note_count07, :note_count08, :note_count09, :note_count10,"
	    			":note_count, :note_amount,"
	                ":status"
                ")";
    query = QSqlQuery(m_db);
    query.prepare(query_str);
    query.bindValue(":type", trans_info.note_info.trans_type);
    query.bindValue(":epoch", trans_info.date_time.toSecsSinceEpoch());
    query.bindValue(":user_name", trans_info.user_name);
    query.bindValue(":client_name", trans_info.client_name);
    query.bindValue(":terminal_name", trans_info.terminal_name);
    query.bindValue(":terminal_id", trans_info.terminal_id);
    query.bindValue(":note_count01", trans_info.note_info.note_count[0]);
    query.bindValue(":note_count02", trans_info.note_info.note_count[1]);
    query.bindValue(":note_count03", trans_info.note_info.note_count[2]);
    query.bindValue(":note_count04", trans_info.note_info.note_count[3]);
    query.bindValue(":note_count05", trans_info.note_info.note_count[4]);
    query.bindValue(":note_count06", trans_info.note_info.note_count[5]);
    query.bindValue(":note_count07", trans_info.note_info.note_count[6]);
    query.bindValue(":note_count08", trans_info.note_info.note_count[7]);
    query.bindValue(":note_count09", trans_info.note_info.note_count[8]);
    query.bindValue(":note_count10", trans_info.note_info.note_count[9]);
    query.bindValue(":note_count", trans_info.note_info.total_count);
    query.bindValue(":note_amount", trans_info.note_info.total_amount);
    query.bindValue(":status", trans_info.status);
    if (!query.exec())
    	qInfo() << "[STORAGE]Append record failed";
}

bool DataStorage::db_op_exists(QString op_name)
{
    QString query_str;
	QSqlQuery query;

	query_str = QString(
					"SELECT password FROM operator "
					"WHERE name = \"%1\"").arg(op_name);
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();

	if (!query.next())
		return false;

    return true;
}

bool DataStorage::db_op_exists(uint32_t finger_id)
{
    QString query_str;
	QSqlQuery query;

	query_str = QString(
					"SELECT password FROM operator "
					"WHERE fingerid = \"%1\"").arg(finger_id);
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();

	if (!query.next())
		return false;

    return true;
}

bool DataStorage::db_op_append(QString op_name, QString op_password, QString station_id, uint32_t finger_id)
{
    QString query_str;
	QSqlQuery query;

	query_str = "INSERT INTO "
				"operator ("
	    			"name, password, terminal_name, fingerid, isadmin"
	            ") "
	            "VALUES ("
	    			":name, :password, :terminal_name,  :fingerid, :isadmin"
	            ")";
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.bindValue(":name", op_name);
	query.bindValue(":password", op_password);
	query.bindValue(":terminal_name", station_id);
	query.bindValue(":fingerid", finger_id);
	query.bindValue(":isadmin", 0);
    if (!query.exec()) {
    	qInfo() << "[STORAGE]Add user failed";
    	return false;
    }

    return true;
}

void DataStorage::db_op_update_password(QString op_name, QString op_password)
{
    QString query_str;
	QSqlQuery query;

	query_str = QString(
					"UPDATE operator "
					"SET password = \"%1\" "
                    "WHERE name = \"%2\"").arg(op_password, op_name);
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();
}

bool DataStorage::db_op_update(QString op_name, QString op_password, QString station_id, uint32_t finger_id)
{
    QString query_str;
	QSqlQuery query;

	query_str = QString(
					"UPDATE operator "
					"SET password = \"%1\", "
					"terminal_name = \"%2\", "
                    "fingerid = %3 "
                    "WHERE name = \"%4\"").arg(op_password, station_id).arg(finger_id).arg(op_name);
	query = QSqlQuery(m_db);
	query.prepare(query_str);
    if (!query.exec()) {
    	qInfo() << "[STORAGE]Update user failed";
    	return false;
    }

    return true;
}

bool DataStorage::db_op_authenticate(QString op_name, QString op_password)
{
    QString query_str;
	QSqlQuery query;

	query_str = QString(
					"SELECT password FROM operator "
					"WHERE name = \"%1\"").arg(op_name);
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();

	if (!query.next())
		return false;
	if (query.value("password").toString() == op_password)
		return true;

    return false;
}

bool DataStorage::db_op_info(uint32_t finger_id, QString &op_name, QString &op_password)
{
    QString query_str;
	QSqlQuery query;

	query_str = QString("SELECT name, password FROM operator "
					"WHERE fingerid = %1").arg(finger_id);
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();

	if (!query.next())
		return false;
	op_name = query.value("name").toString();
    op_password = query.value("password").toString();
    return true;
}

QString DataStorage::db_op_station_id(QString op_name)
{
    QString query_str;
	QSqlQuery query;

	query_str = QString("SELECT terminal_name FROM operator "
					"WHERE name = \"%1\"").arg(op_name);
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();

	if (!query.next())
		return "";
    return query.value("terminal_name").toString();
}

void DataStorage::db_op_delete(OpInfo op_info)
{
    QString query_str;
	QSqlQuery query;

	query_str = QString("DELETE FROM operator "
                    "WHERE name = \"%1\"").arg(op_info.op_name);
	query = QSqlQuery(m_db);
	query.prepare(query_str);
	query.exec();
}

bool DataStorage::db_op_query()
{
    QString query_str;

    // Inquiry database
    query_str = QString("SELECT * FROM operator WHERE name != \"%1\"").arg(ADMIN_USER_NAME);
    m_query = QSqlQuery(m_db);
    if (!m_query.exec(query_str)) {
    	qInfo() << "[STORAGE]Query table failed" << query_str;
    	return false;
    }

    return true;
}

bool DataStorage::db_op_query_next(OpInfo &op_info)
{
	if (!m_query.next())
        return false;

    db_op_fetch_row(op_info);
    return true;
}

bool DataStorage::db_op_query_nth(uint32_t idx, OpInfo &op_info)
{
	if (m_query.seek(idx - 1)) {
    	db_op_fetch_row(op_info);
		return true;
	}

	return false;
}

void DataStorage::db_op_fetch_row(OpInfo &op_info)
{
	op_info.id = m_query.value("rowid").toUInt();
	op_info.op_name = m_query.value("name").toString();
	op_info.op_password = m_query.value("password").toString();
	op_info.terminal_name = m_query.value("terminal_name").toString();
    op_info.op_finger_id = m_query.value("fingerid").toUInt();
}

void DataStorage::db_clr_append(ClearInfo clr_info)
{
    QString query_str;

    // Inquiry database
    query_str = "INSERT INTO "
    			"clrec ("
	    			"epoch, note_count, note_amount, coin_count, coin_amount, amount"
	            ") "
                "VALUES ("
	    			":epoch, :note_count, :note_amount, :coin_count, :coin_amount, :amount"
                ")";
    m_query = QSqlQuery(m_db);
    m_query.prepare(query_str);
    m_query.bindValue(":epoch", clr_info.date_time.toSecsSinceEpoch());
    m_query.bindValue(":note_count", clr_info.note_count);
    m_query.bindValue(":note_amount", clr_info.note_amount);
    m_query.bindValue(":coin_count", clr_info.coin_count);
    m_query.bindValue(":coin_amount", clr_info.coin_amount);
    m_query.bindValue(":amount", clr_info.amount);
    if (!m_query.exec())
		qInfo() << "[STORAGE]Append record failed";
}

bool DataStorage::db_clr_query(QDate start, QDate stop)
{
	qint64 epoch_start, epoch_stop;
    QString query_str;

    epoch_start = start.startOfDay().toSecsSinceEpoch();
    epoch_stop = stop.endOfDay().toSecsSinceEpoch();
    // Inquiry database
    query_str = QString("SELECT * FROM clrec WHERE epoch >= %1 AND epoch <= %2 ORDER BY epoch").arg(epoch_start).arg(epoch_stop);
    m_query = QSqlQuery(m_db);
    if (!m_query.exec(query_str)) {
    	qInfo() << "[STORAGE]Query table failed" << query_str;
    	return false;
    }

    return true;
}

bool DataStorage::db_clr_query_next(ClearInfo &clr_info)
{
	if (!m_query.next())
        return false;

    db_clr_fetch_row(clr_info);
    return true;
}

bool DataStorage::db_clr_query_nth(uint32_t idx, ClearInfo &clr_info)
{
	if (m_query.seek(idx - 1)) {
    	db_clr_fetch_row(clr_info);
		return true;
	}

	return false;
}

void DataStorage::db_clr_fetch_row(ClearInfo &clr_info)
{
	clr_info.date_time = QDateTime::fromSecsSinceEpoch(m_query.value("epoch").toUInt());
    clr_info.note_count = m_query.value("note_count").toUInt();
    clr_info.note_amount = m_query.value("note_amount").toUInt();
    clr_info.coin_count = m_query.value("coin_count").toUInt();
    clr_info.coin_amount = m_query.value("coin_amount").toUInt();
    clr_info.amount = m_query.value("amount").toUInt();
}

void DataStorage::db_test()
{
	TransInfo trans_info;
	uint32_t total_count, total_amount;

	memset(&trans_info.note_info, 0, sizeof(trans_info.note_info));
	for (int k = 0; k < 10; k++) {
        trans_info.date_time = QDate(2024, 5, k + 1).startOfDay();
		trans_info.user_name = Coordinator::coordinator()->get_user_name();
		trans_info.client_name = Coordinator::coordinator()->get_client_name();
		trans_info.terminal_name = Coordinator::coordinator()->get_terminal_name();
		trans_info.terminal_id = Coordinator::coordinator()->get_terminal_id();

		total_count = total_amount = 0;
		trans_info.note_info.trans_type = rand() % 2;
		for (int n = 0; n < NOTE_TYPE_COUNT - 4; n++) {
			trans_info.note_info.note_count[n] = k + 1;
            trans_info.note_info.note_amount[n] = (k + 1) * NOTE_factor(n);
			total_count += k + 1;
            total_amount += (k + 1) * NOTE_factor(n);
		}
		trans_info.note_info.total_count = total_count;
		trans_info.note_info.total_amount = total_amount;
        trans_info.status = TRANS_STATUS_NOT_UPLOADED;

        db_append_sub_transaction(trans_info);
	}

	db_query(QDate(2024, 4, 3), QDate(2024, 4, 7));

	while (db_query_next(trans_info)) {
        qInfo() << "[STORAGE]DateTime:" << trans_info.date_time.toString("yyyy-MM-dd hh:mm:ss");
	}
}
